package com.es.hr.shopAdjust;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class ShopAdjustBiz {

	public PageBean queryEmpList(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp.emp_id,emp.emp_area_value,emp.emp_province_value,emp.emp_city_value,emp.emp_name, "
					+ " (select top 1 common_value from hrm_common where common_code = emp.emp_role and common_type='role') emp_role, " 
					+ "(select top 1 a.emp_name from hrm_emp_info a where emp.emp_ctm_code = a.emp_id) ctm_name, "
					+ "emp.emp_code,emp.emp_job,emp.emp_phone_mobile,emp.shop_name,emp.shop_code  " 
					+ " from hrm_emp_info emp where emp.del_flag='N' ";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%' ",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.shop_code) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.shop_name) like '%'+UPPER(?)+'%'"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")) {
					sql += sqlArrary[i];
					query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
	}
	
	public PageBean queryEmpHistoryList(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select h.emp_id,new_shop_code,new_shop_value,old_shop_code,old_shop_value,shop_adjust_date,shop_adjust_bak , "
					+ " map.emp_name,map.emp_role,map.emp_area_value,map.emp_province_value,map.emp_city_value,map.emp_code " 
					+ "from hrm_shop_adjust_history h,hrm_emp_info map "
					+ "where h.emp_id= map.emp_id  " ;
					//+ " order by h.emp_id,shop_adjust_date";
			String[] sqlArrary = new String[] { "and map.emp_area_code=? ",
					"and map.emp_province_code=? ", "and map.emp_city_code=? ",
					"and UPPER(map.emp_code) like '%'+UPPER(?)+'%' ",
					"and map.emp_role=? ",
					"and UPPER(map.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(map.shop_code) like '%'+UPPER(?)+'%'",
					"and UPPER(map.shop_name) like '%'+UPPER(?)+'%'"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")) {
					sql += sqlArrary[i];
					query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
	}
	
	public PageBean queryShopForPop(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select shop.shop_id,shop.shop_code,shop.shop_name,shop.shop_level,shop.shop_area_value,shop.shop_province_value,shop.shop_city_value,shop.shop_citys_value, "
					+ " (select top 1 a.emp_name from hrm_emp_info a where shop.shop_ctm_code = a.emp_id)  shop_ctm_name,shop.shop_ctm_code " 
					+ "from (select emp.shop_code from hrm_emp_info emp where emp.emp_id=?)emp,hrm_shop_info shop "
					+ " where shop.shop_code != emp.shop_code ";
			String[] sqlArrary = new String[] { "and shop.shop_area_code=? ",
					"and shop.shop_province_code=? ", "and shop.shop_city_code=? ",
					"and UPPER(shop.shop_code) like '%'+UPPER(?)+'%'",
					"and UPPER(shop.shop_name) like '%'+UPPER(?)+'%'"};
			query.add(param.get(0).trim());
			for (int i = 1; i < param.size(); ++i) {
				if (!param.get(i).equals("")) {
					sql += sqlArrary[i-1];
					query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
		}
	
	public HashMap<String,String> getEmpArea(String emp_id)	throws Exception {
		Dao dao = new Dao();
		HashMap<String,String> map = new HashMap<String,String>();
		ArrayList<String> query = new ArrayList<String>();
		query.add(emp_id);
		try {
			String sql = "select emp_area_code,emp_province_code,emp_city_code from hrm_emp_info where emp_id=? ";
			ResultSet rs = dao.Select(sql, query);
			
			if(rs.next()){
				map.put("emp_area_code", rs.getString("emp_area_code"));
				map.put("emp_province_code", rs.getString("emp_province_code"));
				map.put("emp_city_code", rs.getString("emp_city_code"));
			}
			
		} finally {
			dao.close();
		}
		return map;
		}
	
	public ArrayList<HashMap<String,String>> EmpAdjustHistoryForPop(String emp_id)	throws Exception {
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> query = new ArrayList<String>();
		query.add(emp_id);
		try {
			String sql = "select new_shop_code,new_shop_value,old_shop_code,old_shop_value,shop_adjust_date,shop_adjust_bak "+
				"from hrm_shop_adjust_history where emp_id=? order by shop_adjust_date";
			ResultSet rs = dao.Select(sql, query);
			
			while(rs.next()){
				HashMap<String,String> map = new HashMap<String,String>();
				map.put("new_shop_code", rs.getString("new_shop_code"));
				map.put("new_shop_value", rs.getString("new_shop_value"));
				map.put("old_shop_code", rs.getString("old_shop_code"));
				map.put("old_shop_value", rs.getString("old_shop_value"));
				map.put("shop_adjust_date", rs.getString("shop_adjust_date"));
				map.put("shop_adjust_bak", rs.getString("shop_adjust_bak"));
				list.add(map);
			}
			
		} finally {
			dao.close();
		}
		return list;
		}
	
	public boolean adjustShopDo(String emp_id,String shop_id,String shop_adjust_bak) throws Exception{
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-dd" );    
		String datetime = tempDate.format(new java.util.Date());  
		HashMap<String,String> map = new HashMap<String,String> ();
		query.add(emp_id);
		query.add(emp_id);
		query.add(shop_id);
		String queryShopSql="select   (select top 1 shop.shop_code from hrm_emp_info emp,hrm_shop_info shop	where emp.shop_code= shop.shop_code and emp_id=?) old_shop_code, "+
			"(select top 1 shop.shop_name from hrm_emp_info emp,hrm_shop_info shop	where emp.shop_code= shop.shop_code and emp_id=?) old_shop_value, "+
			" shop.shop_code new_shop_code ,shop.shop_name new_shop_value,shop_ctm_code from hrm_shop_info shop where shop_id=?";
		String historySql="insert into  hrm_shop_adjust_history(emp_id,old_shop_code,new_shop_code,shop_adjust_date,shop_adjust_bak,old_shop_value,new_shop_value) values(?,?,?,?,?,?,?)";//7个？
		String empSql="update hrm_emp_info set shop_code=?,shop_name=?,emp_ctm_id=? where emp_id=?";
		try {
			ResultSet rs = dao.Select(queryShopSql, query);
			if(rs.next()){				
				map.put("old_shop_code", rs.getString("old_shop_code"));
				map.put("old_shop_value", rs.getString("old_shop_value"));
				map.put("new_shop_code", rs.getString("new_shop_code"));
				map.put("new_shop_value", rs.getString("new_shop_value"));
				map.put("shop_ctm_code", rs.getString("shop_ctm_code"));
			}
			if(map.size()>0){
				query.clear();
				query.add(emp_id);
				query.add(map.get("old_shop_code"));
				query.add(map.get("new_shop_code"));
				query.add(datetime);
				query.add(shop_adjust_bak);
				query.add(map.get("old_shop_value"));
				query.add(map.get("new_shop_value"));
				dao.update(historySql, query);
				
				query.clear();
				query.add(map.get("new_shop_code"));
				query.add(map.get("new_shop_value"));
				query.add(map.get("shop_ctm_code"));
				query.add(emp_id);
				dao.update(empSql, query);
				
			}
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	}

